package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import com.sl.au.entity.QuoteForeign;
import com.sl.au.entity.QuoteReplacement;


@Transactional(readOnly = true)
public interface QuoteForeignRepository extends JpaRepository<QuoteForeign, String > {
	/**
	 * 外贸价格单根据项目名称,客户名称或当前状态查询所有
	 * @param key 查询条件
	 * @param pageable
	 * @return page
	 */
	@Query(" select f from QuoteForeign f where (f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%') and (f.auditState <> '通过' and f.auditState <> '驳回') ")
	Page<QuoteForeign> findAllQuoteForeignByKey(String key, Pageable pageable);
	@Query(" select f from QuoteForeign f where (f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')  ")
	Page<QuoteForeign> findAllQuoteForeignByKeyAll(String key, Pageable pageable);
	
	@Query(" select f from QuoteForeign f where (f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%') and f.auditState = ?2 ")
	Page<QuoteForeign> findAllQuoteForeignByKey(String key,String auditState, Pageable pageable);
	/**
	 * 得到所有数据查询条数
	 * @param key 查询条件
	 * @return int
	 */
	@Query("select count(*) from QuoteForeign f where (f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%') ")
	long findAllCount(String key);
	@Query("select count(*) from QuoteForeign f where (f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%') and (f.auditState <> '通过' and f.auditState <> '驳回')")
	long findAllCountwsp(String key);
	@Query("select count(*) from QuoteForeign f where (f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%') and f.auditState = ?2")
	long findAllCount(String key,String auditState);
	/**
	 * 得到本部门数据
	 * @param key 查询条件
	 * @param superior 部门
	 * @return page
	 */
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.saleArea.superior =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and (f.auditState <> '通过' and f.auditState <> '驳回') ")
	Page<QuoteForeign> findSuperiorPage(String key, String superior, String userId,Pageable pageable);
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.saleArea.superior =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) ")
	Page<QuoteForeign> findSuperiorPageAll(String key, String superior, String userId,Pageable pageable);
	
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.saleArea.superior =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and f.auditState = ?4")
	Page<QuoteForeign> findSuperiorPage(String key, String superior, String userId,String auditState,Pageable pageable);
	/**
	 * 得到按本部门过滤的数据条数
	 */
	@Query(" select count(*) from QuoteForeign f where (f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.superior =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))")
	long findSuperiorCount(String key,String superior,String userId);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.superior =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and f.auditState=?4")
	long findSuperiorCount(String key,String superior,String userId,String auditState);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.superior =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and (f.auditState <> '通过' and f.auditState <> '驳回')")
	long findSuperiorCountwsp(String key,String superior,String userId);
	/**
	 * 本片区过滤数据
	 * @param key 查询条件
	 * @param areaId saleAreaId
	 * @return
	 */
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and (f.auditState <> '通过' and f.auditState <> '驳回')")
	Page<QuoteForeign> finAreaPage(String key, String areaId,String userId,Pageable pageable);
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过')))")
	Page<QuoteForeign> finAreaPageAll(String key, String areaId,String userId,Pageable pageable);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过')))")
	long findAreaCount(String key,String areaId,String userId);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and (f.auditState <> '通过' and f.auditState <> '驳回')")
	long findAreaCountwsp(String key,String areaId,String userId);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and f.auditState = ?4")
	long findAreaCountwsp(String key,String areaId,String userId,String auditState);
	
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and f.auditState = ?4")
	Page<QuoteForeign> finAreaPage(String key, String areaId,String userId,String auditState,Pageable pageable);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))) and f.auditState = ?4")
	long findAreaCount(String key,String areaId,String userId,String auditState);
	/**
	 * 按本人过滤数据
	 * @param key 查询条件
	 * @param userId 创建者 user id
	 * @return page
	 */
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.id =?2) and (f.auditState <> '通过' and f.auditState <> '驳回') ")
	Page<QuoteForeign> findSelfPage(String key, String userId, Pageable pageable);
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.id =?2)  ")
	Page<QuoteForeign> findSelfPageAll(String key, String userId, Pageable pageable);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.id =?2) and (f.auditState <> '通过' and f.auditState <> '驳回') ")
	long findSelfCountwsp(String key,String userId);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.id =?2) ")
	long findSelfCount(String key,String userId);
	
	@Query(" select f from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%' or f.customerName like '%'+?1+'%')"
			+ "and f.user.id =?2) and f.auditState = ?3 ")
	Page<QuoteForeign> findSelfPage(String key, String userId,String auditState, Pageable pageable);
	@Query(" select count(*) from QuoteForeign f where ((f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.id =?2) and f.auditState = ?3 ")
	long findSelfCount(String key,String userId,String auditState);
	
	
	/**
	 * 根据外贸价格单Id得到其他细目
	 * @param quoteForeignId 外贸价格单Id
	 * @return
	 */
	@Query(" select q from QuoteReplacement q where q.quoteForeign.id=?1")
	Page<QuoteReplacement> findQuoteReplacement(String quoteForeignId, Pageable pageable);
	/**
	 * 根据外贸价格单得到其他细目条数
	 * @param quoteForeignId  外贸价格单Id
	 * @return
	 */
	@Query(" select count(*) from QuoteReplacement q where q.quoteForeign.id=?1")
	long getReplacementCount(String quoteForeignId);
	/**
	 * 根据外贸价格单得到其他细目List
	 * @param quoteForeignId 外贸价格单Id
	 * @return
	 */
	@Query(" select q from QuoteReplacement q where q.quoteForeign.id=?1")
	List<QuoteReplacement> findReplacementByQuoteForeignId(String quoteForeignId);
	
	/**
	 * 得到本部门某一种审批审批状态下的报价单数据
	 * @param key 查询条件
	 * @param superior 部门
	 * 
	 * @return page
	 */
	@Query(" select f from QuoteForeign f where f.name like '%'+?1+'%' and f.auditState =?2 "
			+ "and f.user.saleArea.superior =?3")
	Page<QuoteForeign> getAuditQuotes(String key, String auditState, String superior, Pageable pageable);
	
	@Query(" select count(*) from QuoteForeign f where f.name like '%'+?1+'%' and f.auditState =?2 "
			+ "and f.user.saleArea.superior =?3")
	long getAuditQuoteCount(String key, String auditState, String superior);
	
	/**
	 * 根据普通价格单Id得到其他细目QuoteReplacement
	 * 
	 * @param quoteForeignId
	 *            普通价格单Id
	 * @param string 
	 * @return page
	 */
	@Query(" select q from QuoteReplacement q where q.quoteForeign.id=?1 and (q.type like '%'+?2+'%' or "
			+ "q.deviceName like '%'+?2+'%' or q.material like '%'+?2+'%') ")
	Page<QuoteReplacement> findQuoteReplacementER(String quoteForeignId, String key, Pageable pageable);

	/**
	 * 根据普通价格单Id得到其他细目条数
	 */
	@Query(" select count(*) from QuoteReplacement q where q.quoteForeign.id=?1 and (q.type like '%'+?2+'%' or "
			+ "q.deviceName like '%'+?2+'%' or q.material like '%'+?2+'%') ")
	long getReplacementCountER(String quoteForeignId, String key);

	
	@Query(" select count(*) from QuoteReplacement q where q.type like '%'+?1+'%' and "
			+ "q.deviceName like '%'+?2+'%' and q.material like '%'+?3+'%' ")
	long getReplacementCountERprice(String type, String deviceName, String material);

	@Query(" select q from QuoteReplacement q where q.type like '%'+?1+'%' and "
			+ "q.deviceName like '%'+?2+'%' and q.material like '%'+?3+'%' ")
	Page<QuoteReplacement> findQuoteReplacementERprice(String type, String deviceName, String material, Pageable pageable);


}
